﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using SERIX.Report;
using SERIX.Util;
using System.Data.OracleClient;

namespace SERIX
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        String Relatorio;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                BiblioSERIX.PreencherDdl(ref ddlTpoBenef, CTiposBeneficiarios.ObterTodos("ADM"), "DSC_TPO_BENEF", "IDT_TPO_BENEF");
            }
        }

        protected void btnGerar_Click(object sender, EventArgs e)
        {
            if (TabContainer1.TabIndex == 0)
            {
               Response.Redirect(Report.Report.ImprimirRelatorio(ddlFormato.SelectedValue.ToString(), Beneficiario(), Relatorio,true));
            }
        }

        DataSet Beneficiario()
        {
            DataSet ds = new DataSet();
            ds.ReadXmlSchema(Server.MapPath("/Report/DataSet/Beneficiario.xsd"));
            OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            OracleDataAdapter dt1;
            Relatorio = "~/Report/Benef.rpt";

            try
            {
                conn.Open();

                string Beneficiario = @"select b.*,j.apelido_conjuge,j.apelido_conjuge,j.cpf_conjuge,j.datanasc_conjuge,j.escolaridade_conjuge,j.munnasc_conjuge,j.nome_conjuge,j.rg_conjuge,j.sexo_conjuge,j.situacao,j.status as status_conjuge,j.ufnasc_conjuge, t.dsc_tpo_benef from srx_beneficiarios b
                                        CONDICIONAL srx_conjuge j on (b.idt_conjuge = j.idt_conjuge)
                                        left join srx_tipos_beneficiarios t on (b.idt_tpo_benef = t.idt_tpo_benef)
                                        where b.nom_benef like '%"+tbNome.Text+"%'";
               

                /*------Condicional do estado civil-------*/
                if(ddlEstadoCivil.SelectedValue.ToString() == "U" || ddlEstadoCivil.SelectedValue.ToString() == "C")
                {
                    Beneficiario = Beneficiario.Replace("CONDICIONAL", "join");
                }else 
                {
                    Beneficiario = Beneficiario.Replace("CONDICIONAL", "left join");
                    if (ddlEstadoCivil.SelectedIndex.ToString() != "0")
                    {
                        Beneficiario += " and b.idt_conjuge is null";
                    }
                }

                /*------Condicional do sexo-------*/
                if (ddlSexo.SelectedIndex != 0)
                {
                    Beneficiario += "  and b.sexo = '" + ddlSexo.SelectedValue.ToString() + "'";
                }

                /*------Condicional ano de nascimento-------*/
                if (!String.IsNullOrEmpty(tbAnoNasc.Text))
                {
                    Beneficiario += " and b.dat_nasc(sysdate,'YYYY') = " + tbAnoNasc.Text;
                }
                dt1 = new OracleDataAdapter(Beneficiario, conn);
                dt1.Fill(ds, "SRX_BENEFICIARIOS");
                conn.Close(); 
                return ds;
            }
            catch (Exception e)
            {
                return null;
            }
        }
    }
}
